﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;

namespace FoundViewDB
{
    public partial class Form1 : Form
    {
        public string toolName = "FoundViewDB ";

        private SqlConnection sqlConnection;    //创建一个SQL数据库连接对象
        private SqlDataAdapter dataAdapter;     //创建一个SqlDataAdapter对象
        private DataTable dataTable;            //创建一个DataTable对象，存储对于数据的引用
        private SqlCommand sqlCmd;              //Sql命令
        
        private string table = "FoundViewTable";//待查询的表

        private Tools T = new Tools();  //辅助功能函数对象
        string[] files;                 //打开的文件名

        List<Iteam> datas = new List<Iteam>();  //基金净值信息
        List<string> keys = new List<string>(); //所有基金代码
        string today_h1 = "今日估值";
        string today_h2 = "今日净值";
        string yestoday_h = "昨日净值";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //到数据库的连接
            sqlConnection = new SqlConnection();
            string CurDir = System.AppDomain.CurrentDomain.BaseDirectory;
            sqlConnection.ConnectionString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=" + CurDir + "FoundViewDB.mdf;Integrated Security=True";
            sqlConnection.Open();

            //数据适配器
            dataAdapter = new SqlDataAdapter("Select * from " + table, sqlConnection);
            new SqlCommandBuilder(dataAdapter);    //为dataAdapter添加命令自动生成对象

            //读取数据库中的数据到dataTable中
            dataTable = new DataTable();
            dataAdapter.Fill(dataTable);           //向数据表中填充数据
            dataGridView.DataSource = dataTable;   //显示表数据
            getKeys();                             //获取已有基金代码

            //创建到数据库的命令对象
            sqlCmd = new SqlCommand();
            sqlCmd.Connection = sqlConnection;
        }

        //获取所有基金代码信息
        private void getKeys()
        {
            string key = "key";
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                key = Str(dataTable.Rows[i].ItemArray[0]);
                if (!keys.Contains(key)) keys.Add(key);
            }
        }

        //执行Sql命令，显示
        private int execute(string SqlCmd)
        {
            try
            {
                sqlCmd.CommandText = SqlCmd;
                 return sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex) { return 0; }
        }

        //执行Sql命令，显示
        private int Execute(string SqlCmd)
        {
            int i = execute(SqlCmd);

            dataTable.Clear();
            dataAdapter.Fill(dataTable);

            return i;
        }

        private void 保存ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            dataAdapter.Update(dataTable);   //更新修改
        }

        private void 添加列ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Execute("alter table " + table + " add 基金名称 FLOAT NULL");
        }

        private void 删除列ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                int index = dataGridView.SelectedCells[0].ColumnIndex;
                string columnName = dataGridView.Columns[index].Name;

                Execute("alter table " + table + " drop column " + columnName);
                //dataGridView.Columns.Remove(columnName);
            }
            catch (Exception ex) { }
        }

        private void 添加行ToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            //string code = Str(dataGridView.SelectedRows[0].Cells[0].Value);

            //string key = dataTable.Columns[0].ColumnName;
            //Execute("insert into " + table + "(" + key + ") Values('" + code + "')");

            //dataGridView.Rows.Remove(dataGridView.SelectedRows[0]);
        }

        private void 删除行ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                string key = dataTable.Columns[0].ColumnName;
                int index = dataGridView.SelectedCells[0].RowIndex;
                string code = Str(dataGridView.Rows[index].Cells[0].Value);

                Execute("delete from " + table + " where " + key + " = '" + code + "'");
            }
            catch (Exception ex) { }

            //string key = dataTable.Columns[0].ColumnName;

            //DataGridViewSelectedRowCollection rows = dataGridView.SelectedRows;

            //foreach (DataGridViewRow row in rows)
            //{
            //    string code = row.Cells[0].ToString();
            //    execute("delete from " + table + " where " + key + " = '" + code + "'");
            //}
            //dataTable.Clear();
            //dataAdapter.Fill(dataTable);
        }

        private void 修改行ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string key = dataTable.Columns[0].ColumnName;
            string clom2 = dataTable.Columns[1].ColumnName;
            string clom3 = dataTable.Columns[2].ColumnName;

            Execute("update " + table + " set " + clom2 + " = 'clom2', [" + clom3 + "] = '3.24' where " + key + " = '1042163'");

        }


        private void addValue(string code, string name, string date, float value)
        {
            string key = dataTable.Columns[0].ColumnName;
            string clom2 = dataTable.Columns[1].ColumnName;
            //string clom2 = "基金名称";
            
            //添加新的列
                execute("alter table " + table + " add " + date + " FLOAT NULL");

            //添加新的行
                execute("insert into " + table + "(" + key + ") Values('" + code + "')");
                //execute("insert into " + table + "(" + key + ", [" + clom2 + "], [" + date + "]) Values(N'" + code + "', N'" + name + "','" + value + "')");
            
            //else 
                //execute("update " + table + " set [" + date + "] = '" + value + "' where " + key + " = '" + code + "'");
                execute("update " + table + " set [" + date + "] = '" + value + "',[" + clom2 + "] = N'" + name + "' where " + key + " = '" + code + "'");
        }

        //添加新的列
        private void addNewColumFloat(string date)
        {
            if (!dataGridView.Columns.Contains(date))
            execute("alter table " + table + " add " + date + " FLOAT NULL");
        }

        //添加新的行
        private void addNewRow(string code)
        {
            if (!keys.Contains(code))
            {
                keys.Add(code);
                string key = dataTable.Columns[0].ColumnName;
                execute("insert into " + table + "(" + key + ") Values('" + code + "')");
            }
        }

        //修改基金名称
        private void updateName(string code, string name)
        {
            addNewRow(code);

            string key = dataTable.Columns[0].ColumnName;
            string clom2 = dataTable.Columns[1].ColumnName;
            execute("update " + table + " set [" + clom2 + "] = N'" + name + "' where " + key + " = '" + code + "'");
        }


        //修改基金净值
        private void updateValue(string code, string date, float value)
        {
            addNewRow(code);

            string key = dataTable.Columns[0].ColumnName;
            execute("update " + table + " set [" + date + "] = '" + value + "' where " + key + " = '" + code + "'");
        }


        private void Form1_DragEnter(object sender, DragEventArgs e)
        {
            T.dragEnter(e);
        }

        private void Form1_DragDrop(object sender, DragEventArgs e)
        {
            string str = T.dragDrop(e);
            files = str.Split(';');

            this.Text = toolName + "向数据库添加信息中...";
            foreach(string file in files) saveToDB(file);
            this.Text = toolName;

            MessageBox.Show("信息已添加到数据库中");
        }


        //保存文件信息到数据库
        private void saveToDB(string file)
        {
            string str = T.fileToString(file);  //读取文件数据

            if(file.EndsWith(".htm"))
            {
                analysis(str);
                panel1.Visible = true;
            }
            else if (file.EndsWith(".txt"))
            {
                string[] tmp = str.Split(':');      //分割标题和正文
                this.Text = tmp[0];                 //设置标题
                string date = tmp[0].Split(' ')[0]; //获取当前文件日期信息

                string[][] data = T.ToArray(tmp[1]);//分割正文信息

                foreach (string[] D in data)        //添加信息到数据库中
                    addValue(D[0], D[1], "D" + date, T.Parse(D[4]));

                dataTable.Clear();
                dataAdapter.Fill(dataTable);
            }
        }

        //保存文件信息到数据库
        private void saveToDB(List<Iteam> datas, int index)
        {
            string date = "";
            if (index == 0) date = yestoday_h;
            else if (index == 1) date = today_h1;
            else if (index == 2) date = today_h2;
            date = "D" + date;

            addNewColumFloat(date);             //添加新的列

            foreach (Iteam I in datas)          //添加信息到数据库中
            {
                addNewRow(I.code);              //添加行
                if(checkBox1.Checked)
                    updateName(I.code, I.name);             //修改基金名称
                updateValue(I.code, date, I.value[index]);  //修改净值
            }

            dataTable.Clear();
            dataAdapter.Fill(dataTable);
        }

        private string Str(Object obj)
        {
            if (obj == null) return "";
            else return obj.ToString().Trim();
        }

        private void label1_Click(object sender, EventArgs e)
        {
            textBox1.Text = "alter table " + table + " drop column 涨跌幅度";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Execute(textBox1.Text);
        }

        private void dataGridView_RowHeaderMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            int index = dataGridView.SelectedCells[0].RowIndex;
            string code = Str(dataGridView.Rows[index].Cells[0].Value);
            //string code = dataGridView.SelectedRows[0].Cells[0].Value.ToString().Trim();
            string link = "http://www.howbuy.com/fund/" + code;
            System.Diagnostics.Process.Start(link);
        }

        //获取指定串值对的数据
        private string getData(string data, string start, string end)
        {
            int i1 = data.IndexOf(start) + start.Length, i2 = data.IndexOf(end, i1);
            string tmp = data.Substring(i1, i2 - i1);

            return tmp;
        }

        //获取指定串值对的数据
        private void getDatas(string data, string start, string end, List<string> textareas)
        {
            string tmp = "";
            int i1 = 0, i2 = 0, i = 0;

            while(true)
            {
                i = data.IndexOf(start, i);
                if (i == -1) break;

                i1 = i + start.Length;
                i2 = data.IndexOf(end, i1);

                tmp = data.Substring(i1, i2 - i1);
                textareas.Add(tmp);

                i = i2 + end.Length;
            }
        }

        private void 实时净值ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Text = toolName + "获取净值中...";

            string url = "http://www.howbuy.com/fund/valuation/index.htm";
            string data = T.getWebData(url);

            //获取基金信息数据
            string tabData = getData(data, "<!-- tab 切换 begin -->", "<!-- tab 切换 end -->");
            string nTab2_Con1 = getData(tabData, "<div id=\"nTab2_Con1\" class=\"fund_list result_list\">", "<script>");

            //table1解析
            string today_h1 = getData(nTab2_Con1, "<td colspan=\"2\" class=\"w5 suib bk\">", "</td>");  //估算净值
            string CurDir = T.SaveProcess(data, today_h1);       //保存到文件

            this.Text = toolName;
            T.MessageWithOpen("净值已获取", CurDir);

            analysis(data);
            panel1.Visible = true;
        }

        //分析净值信息文件，存储净值信息到datas中
        private void analysis(string data)
        {
            //清除已有信息
            datas.Clear();

            //获取基金信息数据
            string tabData = getData(data, "<!-- tab 切换 begin -->", "<!-- tab 切换 end -->");
            string nTab2_Con1 = getData(tabData, "<div id=\"nTab2_Con1\" class=\"fund_list result_list\">", "<script>");

            //table1解析
            today_h1 = getData(nTab2_Con1, "<td colspan=\"2\" class=\"w5 suib bk\">", "</td>");              //估算净值
            today_h1 = today_h1.Replace("估算净值", "").Replace("-", "");
            today_h2 = getData(nTab2_Con1, "<td colspan=\"2\" class=\"w6 suib bk\">", "</td>");              //公布净值
            today_h2 = today_h2.Replace("公布净值", "").Replace("-", "");
            yestoday_h = getData(nTab2_Con1, "<td rowspan=\"2\" class=\"w8\">", "</td>").Replace("<br>", "");//昨日净值
            yestoday_h = yestoday_h.Replace("单位净值", "").Replace("-", "");

            //<textarea>
            List<string> textareas = new List<string>();

            string tbody = getData(nTab2_Con1, "<tbody>", "</tbody>");  //净值列表信息
            textareas.Add(tbody);
            getDatas(nTab2_Con1, "<textarea style=\"display:none\">", "</textarea>", textareas);

            foreach (string textarea in textareas)
            {
                //<tr>
                List<string> tbody_trs = new List<string>();                //净值项
                getDatas(textarea, "<tr >", "</tr>", tbody_trs);

                foreach (string tbody_tr in tbody_trs)
                {
                    //<td>
                    List<string> tr_tds = new List<string>();   //项属性列
                    getDatas(tbody_tr, "<td", "</td>", tr_tds);

                    string code = getData(tr_tds[2], "<a target=\"_blank\" href=\"/fund/", "/\">"); //基金代码
                    string name = getData(tr_tds[3], "/\">", "</a>");                               //基金名称

                    string today_v1 = getData(tr_tds[4], "<span class=", "/span>");     //今日估算值
                    today_v1 = getData(today_v1, ">", "<");
                    string today_v2 = tr_tds[6].Substring(1);                           //今日公布值
                    string yestoday_v = getData(tr_tds[9], "<span>", "</span>");        //昨日净值

                    float[] value = new float[] { T.Parse(yestoday_v), T.Parse(today_v1), T.Parse(today_v2) };
                    Iteam I = new Iteam(code, name, value);
                    datas.Add(I);
                }
            }
        }

        private void 添加净值ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            panel1.Visible = !panel1.Visible;  //显示净值添加选项
        }

        //添加对应的净值信息到数据库中
        private void button3_Click(object sender, EventArgs e)
        {
            panel1.Visible = false;
            this.Text = toolName + "正在向本地数据库，添加净值信息中...";

            int index = 2;
            if (radioButton1.Checked) index = 0;
            else if (radioButton2.Checked) index = 1;
            else if (radioButton3.Checked) index = 2;

            saveToDB(datas, index);

            this.Text = toolName;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            addNewColumFloat("涨跌幅度");
            //execute("alter table " + table + " drop column 涨跌幅度");

            int N1 = Int32.Parse(textBox2.Text.Trim()), N2 = Int32.Parse(textBox3.Text.Trim());
            foreach (DataGridViewRow row in dataGridView.Rows)
            {
                float n1 = T.Parse(row.Cells[N1].Value), n2 = T.Parse(row.Cells[N2].Value);
                float num = (n1 == 0 || n2 == 0) ? 0 : ((n2 - n1) / n1 * 100);
                //row.Cells[n - 1].Value = num.ToString("F2");

                updateValue(Str(row.Cells[0].Value), "涨跌幅度", num);
                //addValue(Str(row.Cells[0].Value), Str(row.Cells[1].Value), "涨跌幅度", num);
            }

            dataTable.Clear();
            dataAdapter.Fill(dataTable);
        }

        private void label2_Click(object sender, EventArgs e)
        {
            int index1 = dataGridView.SelectedCells[0].ColumnIndex;
            int index2 = dataGridView.SelectedCells[dataGridView.SelectedCells.Count - 1].ColumnIndex;
            textBox2.Text = Str(index1<index2 ? index1 : index2);
            textBox3.Text = Str(index1 > index2 ? index1 : index2);
        }

        private void 补全净值ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (dataGridView.SelectedCells[0] == null) return;
            this.Text = toolName + "获取补全的净值数据中...";
            
            int index = dataGridView.SelectedCells[0].ColumnIndex;

            foreach (DataGridViewRow row in dataGridView.Rows)
            {
                if (T.Parse(Str(row.Cells[index].Value)) == 0)
                {
                    string code = Str(row.Cells[0].Value);
                    string value = T.getCodeValue(code);
                    string[] v = value.Split(','); // 20150417,1.1164,0.52
                    if (!value.Equals("0,0,0"))
                        updateValue(code, "D" + v[0], T.Parse(v[1]));
                }
                //addValue(Str(row.Cells[0].Value), Str(row.Cells[1].Value), "涨跌幅度", num);
            }

            this.Text = toolName;

            dataTable.Clear();
            dataAdapter.Fill(dataTable);
        }

        //获取选中列净值为0的基金代码
        private void get0valueID()
        {
        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("http://git.oschina.net/scimence/FundViewTool/attach_files");
        }
    }

    public class Iteam
    {
        public string code;        //基金代码
        public string name;        //基金名称
        public float[] value;      //昨日净值、今日估算值、今日公布值

        public Iteam(string code, string name, float[] value)
        {
            // TODO: Complete member initialization
            this.code = code;
            this.name = name;
            this.value = value;
        }
    }
}
